package com.gruppsex.trainingdiary;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Calendar;
import android.content.ContentValues;
import android.content.Context;
import android.content.Intent;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

public class DataBaseHelper extends SQLiteOpenHelper {

	Intent myIntent;
	// The Android's default system path of your application database.
	private static String DB_PATH = "/data/data/com.gruppsex.trainingdairy/databases/";
	private static String DB_NAME = "Training";
	private SQLiteDatabase ourDatabase;
	private final Context ourContext;

	private static final String DATABASE_PROFILE_TABLE = "profile";
	public static final String KEY_PROFIL_ROWID = "_id";
	public static final String KEY_PROFILE_NAME = "name";
	public static final String KEY_PROFILE_AGE = "age";
	public static final String KEY_PROFILE_WEIGHT = "weight";
	public static final String KEY_PROFILE_LENGTH = "length";

	private static final String DATABASE_ACTIVITY_TABLE = "activity";
	public static final String KEY_ACTIVITY_ROWID = "_id";
	public static final String KEY_ACTIVITY_NAME = "name";

	private static final String DATABASE_WORKOUT_TABLE = "workout";
	public static final String KEY_WORKOUT_WORKOUTDAY = "day";
	public static final String KEY_WORKOUT_ACTIVITY = "activity";
	public static final String KEY_WORKOUT_DATE = "date";

	private static final String DATABASE_DAYPASS_TABLE = "daypass";
	public static final String KEY_DAYPASS_ID = "_id";
	public static final String KEY_DAYPASS_CHECKED = "checked";
	public static final String KEY_DAYPASS_DAY = "day";
	public static final String KEY_DAYPASS_ACTIVITY = "activity";
	public static final String KEY_DAYPASS_DATE = "date";
	public static final String KEY_DAYPASS_CALORIES = "calories";
	public static final String KEY_DAYPASS_KM = "km";
	public static final String KEY_DAYPASS_REPS = "reps";
	public static final String KEY_DAYPASS_SETS = "sets";
	public static final String KEY_DAYPASS_DESC = "description";
	public static final String KEY_DAYPASS_TIME = "time";
	public static final String KEY_DAYPASS_WEIGHT = "weight";

	/**
	 * Constructor Takes and keeps a reference of the passed context in order to
	 * access to the application assets and resources.
	 * 
	 * @param context
	 */
	public DataBaseHelper(Context context) {
		super(context, DB_NAME, null, 1);
		this.ourContext = context;
	}

	/**
	 * Creates a empty database on the system and rewrites it with your own
	 * database.
	 * */
	public void createDataBase() throws IOException {

		boolean dbExist = checkDataBase();

		if (dbExist) {
			// do nothing - database already exist
		} else {

			// By calling this method and empty database will be created into
			// the default system path
			// of your application so we are gonna be able to overwrite that
			// database with our database.
			this.getReadableDatabase();

			try {
				copyDataBase();
			} catch (IOException e) {
				throw new Error("Error copying database");
			}
		}
	}

	/**
	 * Check if the database already exist to avoid re-copying the file each
	 * time you open the application.
	 * 
	 * @return true if it exists, false if it doesn't
	 */
	private boolean checkDataBase() {

		SQLiteDatabase checkDB = null;

		try {
			String myPath = DB_PATH + DB_NAME;
			checkDB = SQLiteDatabase.openDatabase(myPath, null,
					SQLiteDatabase.OPEN_READWRITE);
		} catch (SQLiteException e) {
			// database does't exist yet.
		}

		if (checkDB != null) {
			checkDB.close();
		}
		return checkDB != null ? true : false;
	}

	/**
	 * Copies your database from your local assets-folder to the just created
	 * empty database in the system folder, from where it can be accessed and
	 * handled. This is done by transfering bytestream.
	 * */
	private void copyDataBase() throws IOException {

		// Open your local db as the input stream
		InputStream myInput = ourContext.getAssets().open(DB_NAME);

		// Path to the just created empty db
		String outFileName = DB_PATH + DB_NAME;

		// Open the empty db as the output stream
		OutputStream myOutput = new FileOutputStream(outFileName);

		// transfer bytes from the inputfile to the outputfile
		byte[] buffer = new byte[1024];
		int length;
		while ((length = myInput.read(buffer)) > 0) {
			myOutput.write(buffer, 0, length);
		}
		// Close the streams
		myOutput.flush();
		myOutput.close();
		myInput.close();
	}

	public void openDataBase() throws SQLException {
		// Open the database
		String myPath = DB_PATH + DB_NAME;
		ourDatabase = SQLiteDatabase.openDatabase(myPath, null,
				SQLiteDatabase.OPEN_READWRITE);
	}

	@Override
	public synchronized void close() {
		if (ourDatabase != null)
			ourDatabase.close();
		super.close();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	}

	public long createProfileEntry(String name, String age, String weight,
			String length) {
		ContentValues cv = new ContentValues();
		cv.put(KEY_PROFILE_NAME, name);
		cv.put(KEY_PROFILE_AGE, age);
		cv.put(KEY_PROFILE_WEIGHT, weight);
		cv.put(KEY_PROFILE_LENGTH, length);
		return ourDatabase.insert(DATABASE_PROFILE_TABLE, null, cv);
	}

	public long createDescEntry(String activity, int cal, int km, int reps,
			int sets, int time, int weight, int checked, int id, String desc) {
		ContentValues cv = new ContentValues();
		cv.put(KEY_DAYPASS_ACTIVITY, activity);
		cv.put(KEY_DAYPASS_CALORIES, cal);
		cv.put(KEY_DAYPASS_KM, km);
		cv.put(KEY_DAYPASS_REPS, reps);
		cv.put(KEY_DAYPASS_SETS, sets);
		cv.put(KEY_DAYPASS_TIME, time);
		cv.put(KEY_DAYPASS_WEIGHT, weight);
		cv.put(KEY_DAYPASS_CHECKED, checked);
		cv.put(KEY_DAYPASS_DESC, desc);
		Calendar c = Calendar.getInstance();
		int day = c.get(Calendar.DATE);
		int month = c.get(Calendar.MONTH);
		int year = c.get(Calendar.YEAR);
		String mon = "" + (month + 1);
		StringBuilder sbuilder = new StringBuilder().append(day).append("-")
				.append(mon).append("-").append(year).append("");
		String date = sbuilder.toString();
		return ourDatabase.update(DATABASE_DAYPASS_TABLE, cv, "date = '" + date
				+ "'" + " and _id = " + id, null);
	}

	public String getProfile() {
		String[] columns = new String[] { KEY_PROFIL_ROWID, KEY_PROFILE_NAME,
				KEY_PROFILE_AGE, KEY_PROFILE_WEIGHT, KEY_PROFILE_LENGTH };
		Cursor c = ourDatabase.query(DATABASE_PROFILE_TABLE, columns, null,
				null, null, null, null);
		String result = "";
		int iRow = c.getColumnIndex(KEY_PROFIL_ROWID);
		int iName = c.getColumnIndex(KEY_PROFILE_NAME);
		int iAge = c.getColumnIndex(KEY_PROFILE_AGE);
		int iWeight = c.getColumnIndex(KEY_PROFILE_WEIGHT);
		int iLength = c.getColumnIndex(KEY_PROFILE_LENGTH);
		for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
			result = result + c.getString(iRow) + " " + c.getString(iName)
					+ " " + c.getString(iAge) + " " + c.getString(iWeight)
					+ " " + c.getString(iLength) + "\n";
		}
		return result;
	}

	public ArrayList<String> getDayPass(String date) {
		String[] columns = new String[] { KEY_DAYPASS_DAY,
				KEY_DAYPASS_ACTIVITY, KEY_DAYPASS_DATE, KEY_DAYPASS_CALORIES,
				KEY_DAYPASS_KM, KEY_DAYPASS_REPS, KEY_DAYPASS_SETS,
				KEY_DAYPASS_CHECKED, KEY_DAYPASS_DESC };
		ArrayList<String> results = new ArrayList<String>();
		Cursor c = ourDatabase
				.query(DATABASE_DAYPASS_TABLE, columns, "date = '" + date
						+ "' AND checked = 0", null, null, null, null);
		int iActivity = c.getColumnIndex(KEY_DAYPASS_ACTIVITY);
		for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
			results.add(c.getString(iActivity));
		}
		return results;
	}
	
	public ArrayList<Integer> getDayPassID(String date) {
		String[] columns = new String[] {KEY_DAYPASS_ID, KEY_DAYPASS_DAY,
				KEY_DAYPASS_ACTIVITY, KEY_DAYPASS_DATE, KEY_DAYPASS_CALORIES,
				KEY_DAYPASS_KM, KEY_DAYPASS_REPS, KEY_DAYPASS_SETS,
				KEY_DAYPASS_CHECKED, KEY_DAYPASS_DESC };
		ArrayList<Integer> results = new ArrayList<Integer>();
		Cursor c = ourDatabase
				.query(DATABASE_DAYPASS_TABLE, columns, "date = '" + date
						+ "' AND checked = 0", null, null, null, null);
		int iID = c.getColumnIndex(KEY_DAYPASS_ID);
		for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
			results.add(c.getInt(iID));
		}
		return results;
	}

	public ArrayList<String> getDayPassAtDate(String date) {
		String[] columns = new String[] { KEY_DAYPASS_DAY,
				KEY_DAYPASS_ACTIVITY, KEY_DAYPASS_DATE, KEY_DAYPASS_CALORIES,
				KEY_DAYPASS_KM, KEY_DAYPASS_REPS, KEY_DAYPASS_SETS,
				KEY_DAYPASS_TIME, KEY_DAYPASS_WEIGHT, KEY_DAYPASS_DESC };
		Cursor c = ourDatabase.query(DATABASE_DAYPASS_TABLE, columns,
				"date = '" + date + "'", null, null, null, null);
		int iActivity = c.getColumnIndex(KEY_DAYPASS_ACTIVITY);
		int iCal = c.getColumnIndex(KEY_DAYPASS_CALORIES);
		int iKm = c.getColumnIndex(KEY_DAYPASS_KM);
		int iReps = c.getColumnIndex(KEY_DAYPASS_REPS);
		int iSets = c.getColumnIndex(KEY_DAYPASS_SETS);
		int iTime = c.getColumnIndex(KEY_DAYPASS_TIME);
		int iWeight = c.getColumnIndex(KEY_DAYPASS_WEIGHT);
		int iDesc = c.getColumnIndex(KEY_DAYPASS_DESC);
		String result = "";
		ArrayList<String> sum = new ArrayList<String>();
		for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
			result = c.getString(iActivity) + "\nCalories: "
					+ c.getString(iCal) + "\t Kilometers: " + c.getString(iKm)
					+ "\t Time: " + c.getString(iTime) + "\nReps: "
					+ c.getString(iReps) + "\t Sets: " + c.getString(iSets)
					+ "\t Weight: " + c.getString(iWeight) + "\n"
					+ c.getString(iDesc);
			sum.add(result);
		}
		return sum;
	}

	public String getDayPassDesc() {
		String[] columns = new String[] { KEY_DAYPASS_DAY,
				KEY_DAYPASS_ACTIVITY, KEY_DAYPASS_DATE, KEY_DAYPASS_CALORIES,
				KEY_DAYPASS_KM, KEY_DAYPASS_REPS, KEY_DAYPASS_SETS,
				KEY_DAYPASS_DESC };
		Cursor c = ourDatabase.query(DATABASE_DAYPASS_TABLE, columns, null,
				null, null, null, null);
		int iDay = c.getColumnIndex(KEY_DAYPASS_DAY);
		int iActivity = c.getColumnIndex(KEY_DAYPASS_ACTIVITY);
		int iDate = c.getColumnIndex(KEY_DAYPASS_DATE);
		int iCal = c.getColumnIndex(KEY_DAYPASS_CALORIES);
		int iKm = c.getColumnIndex(KEY_DAYPASS_KM);
		int iReps = c.getColumnIndex(KEY_DAYPASS_REPS);
		int iSets = c.getColumnIndex(KEY_DAYPASS_SETS);
		int iDesc = c.getColumnIndex(KEY_DAYPASS_DESC);
		String result = "";

		for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
			result = result + c.getString(iDay) + "\n" + c.getString(iActivity)
					+ "\n" + c.getString(iDate) + "\n" + c.getString(iCal)
					+ "\n" + c.getString(iKm) + "\n" + c.getString(iReps)
					+ "\n" + c.getString(iSets) + "\n" + c.getString(iDesc)
					+ "\n\n";
		}
		return result;
	}

	public long createDayPassEntry(String day, String activitys, String date,
			int cal, int km, String desc) {
		ContentValues cv = new ContentValues();
		cv.put(KEY_DAYPASS_DAY, day);
		cv.put(KEY_DAYPASS_ACTIVITY, activitys);
		cv.put(KEY_DAYPASS_DATE, date);
		cv.put(KEY_DAYPASS_CALORIES, cal);
		cv.put(KEY_DAYPASS_KM, km);
		cv.put(KEY_DAYPASS_REPS, 0);
		cv.put(KEY_DAYPASS_SETS, 0);
		cv.put(KEY_DAYPASS_CHECKED, 0);
		cv.put(KEY_DAYPASS_DESC, desc);
		return ourDatabase.insert(DATABASE_DAYPASS_TABLE, null, cv);
	}

	public long createActivityEntry(String name) {
		ContentValues cv = new ContentValues();
		cv.put(KEY_ACTIVITY_NAME, name);
		return ourDatabase.insert(DATABASE_ACTIVITY_TABLE, null, cv);
	}

	public String[] getActivity() {
		String[] columns = new String[] { KEY_ACTIVITY_ROWID, KEY_ACTIVITY_NAME };
		int sqlRows = (int) DatabaseUtils.queryNumEntries(ourDatabase,
				DATABASE_ACTIVITY_TABLE);
		String[] results = new String[sqlRows];
		int i = 0;
		Cursor c = ourDatabase.query(DATABASE_ACTIVITY_TABLE, columns, null,
				null, null, null, null);
		int iName = c.getColumnIndex(KEY_ACTIVITY_NAME);
		for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
			results[i] = c.getString(iName);
			i++;
		}
		return results;
	}

	public long createWorkoutEntry(String day, String activity, String date) {
		ContentValues cv = new ContentValues();
		cv.put(KEY_WORKOUT_WORKOUTDAY, day);
		cv.put(KEY_WORKOUT_ACTIVITY, activity);
		cv.put(KEY_WORKOUT_DATE, date);
		return ourDatabase.insert(DATABASE_WORKOUT_TABLE, null, cv);
	}

	public String getWorkout() {
		String[] columns = new String[] { KEY_WORKOUT_WORKOUTDAY,
				KEY_WORKOUT_ACTIVITY, KEY_WORKOUT_DATE };
		Cursor c = ourDatabase.query(DATABASE_WORKOUT_TABLE, columns, null,
				null, null, null, null);
		String result = "";
		int iDay = c.getColumnIndex(KEY_WORKOUT_WORKOUTDAY);
		int iActivity = c.getColumnIndex(KEY_WORKOUT_ACTIVITY);
		int iDate = c.getColumnIndex(KEY_WORKOUT_DATE);
		for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
			result = result + c.getString(iDay) + " " + c.getString(iActivity)
					+ " " + c.getString(iDate) + "\n";
		}
		return result;
	}

	public int countPass(String activity) {

		// int result = 0;
		Cursor cursor;
		String string = "SELECT * FROM daypass WHERE activity = '" + activity
				+ "'";

		openDataBase();
		cursor = ourDatabase.rawQuery(string, null);
		int result = cursor.getCount();
		close();

		return result;
	}

	public String[] getFind(String date, String activity) {

		Cursor cursor;
		String result[] = new String[countPass(activity)];
		String sql = "SELECT " + date
				+ " FROM daypass WHERE daypass.activity = " + "'" + activity
				+ "'";
		int i = 0;

		openDataBase();

		cursor = ourDatabase.rawQuery(sql, null);
		int iFind = cursor.getColumnIndex(date);

		for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
			result[i] = cursor.getString(iFind);
			i++;
		}
		close();
		return result;
	}

	public double[] getFinds(String date, String activity) {

		Cursor cursor;
		double[] result = new double[countPass(activity)];
		String sql = "SELECT " + date
				+ " FROM daypass WHERE daypass.activity = " + "'" + activity
				+ "'";
		int i = 0;

		openDataBase();

		cursor = ourDatabase.rawQuery(sql, null);
		int iFind = cursor.getColumnIndex(date);
		int r = 0;
		double t = 0.0;
		for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
			r = cursor.getInt(iFind);
			t = (double) r;
			result[i] = t;
			i++;
		}
		close();
		return result;
	}
}
